﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using ExpenseTracker.Entity;

namespace ExpenseTracker.DataAccess
{
    public class ExpenseDb : BaseDb
    {
        public ExpenseDb(ExpenseTracker.Entity.Account account) : base(account) { }

        public void CreateTable()
        {
            using (SQLiteTransaction transaction = Connection.BeginTransaction())
            {
                using (SQLiteCommand command = Connection.CreateCommand())
                {
                    command.CommandText = @"
                        DROP TABLE IF EXISTS Expense;
                        CREATE TABLE Expense
                        (
                           ExpenseID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                           TransactionDate DATETIME NOT NULL,
                           CategoryID INTEGER NOT NULL,
                           Item VARCHAR(50) NOT NULL,
                           [Description] VARCHAR(255),
                           Amount FLOAT NOT NULL DEFAULT '0' ,
                           GST FLOAT DEFAULT '0' ,
                           PST FLOAT DEFAULT '0' ,
                           Total FLOAT NOT NULL DEFAULT '0',
                           IsDisabled BOOLEAN DEFAULT '0' NOT NULL,
                           InsertDateTime DATETIME NOT NULL,
                           UpdateDateTime DATETIME NOT NULL
                        );
                    ";
                    command.ExecuteNonQuery();
                }

                transaction.Commit();
            }
        }

        public void InsertExpense(ExpenseTracker.Entity.Expense expense)
        {
            using (DbTransaction transaction = Connection.BeginTransaction())
            {
                using (SQLiteCommand command = Connection.CreateCommand())
                {
                    SQLiteParameter[] parameters = new SQLiteParameter[9];
                    parameters[0] = new SQLiteParameter(DbType.DateTime);
                    parameters[0].Value = expense.TransactionDate;
                    parameters[1] = new SQLiteParameter(DbType.Int32);
                    parameters[1].Value = expense.CategoryID;
                    parameters[2] = new SQLiteParameter(DbType.String, 50);
                    parameters[2].Value = expense.Item;
                    parameters[3] = new SQLiteParameter(DbType.String, 255);
                    parameters[3].Value = expense.Description;
                    parameters[4] = new SQLiteParameter(DbType.Single);
                    parameters[4].Value = expense.Amount;
                    parameters[5] = new SQLiteParameter(DbType.Single);
                    parameters[5].Value = expense.GST;
                    parameters[6] = new SQLiteParameter(DbType.Single);
                    parameters[6].Value = expense.PST;
                    parameters[7] = new SQLiteParameter(DbType.Single);
                    parameters[7].Value = expense.Total;
                    parameters[8] = new SQLiteParameter(DbType.Boolean);
                    parameters[8].Value = expense.IsDisabled;
                    
                    command.CommandText = @"
                        INSERT INTO [Expense] (TransactionDate, CategoryID, Item, [Description], Amount, GST, PST, Total, IsDisabled, InsertDateTime, UpdateDateTime) 
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                    ";
                    for (int i = 0; i < parameters.Length; i++)
                    {
                        command.Parameters.Add(parameters[i]);
                    }
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

        public void UpdateExpense(ExpenseTracker.Entity.Expense expense)
        {
            using (DbTransaction transaction = Connection.BeginTransaction())
            {
                using (SQLiteCommand command = Connection.CreateCommand())
                {
                    SQLiteParameter[] parameters = new SQLiteParameter[10];
                    parameters[0] = new SQLiteParameter(DbType.DateTime);
                    parameters[0].Value = expense.TransactionDate;
                    parameters[1] = new SQLiteParameter(DbType.Int32);
                    parameters[1].Value = expense.CategoryID;
                    parameters[2] = new SQLiteParameter(DbType.String, 50);
                    parameters[2].Value = expense.Item;
                    parameters[3] = new SQLiteParameter(DbType.String, 255);
                    parameters[3].Value = expense.Description;
                    parameters[4] = new SQLiteParameter(DbType.Single);
                    parameters[4].Value = expense.Amount;
                    parameters[5] = new SQLiteParameter(DbType.Single);
                    parameters[5].Value = expense.GST;
                    parameters[6] = new SQLiteParameter(DbType.Single);
                    parameters[6].Value = expense.PST;
                    parameters[7] = new SQLiteParameter(DbType.Single);
                    parameters[7].Value = expense.Total;
                    parameters[8] = new SQLiteParameter(DbType.Boolean);
                    parameters[8].Value = expense.IsDisabled;
                    parameters[9] = new SQLiteParameter(DbType.Int32);
                    parameters[9].Value = expense.ExpenseID;                    

                    command.CommandText = @"
                        UPDATE [Expense] 
                        SET 
                            TransactionDate = ?, 
                            CategoryID = ?,
                            Item = ?,
                            [Description] = ?,
                            Amount = ?, 
                            GST = ?,
                            PST = ?,
                            Total = ?,
                            IsDisabled = ?,
                            UpdateDateTime = CURRENT_TIMESTAMP 
                        WHERE ExpenseID = ?
                    ";
                    for (int i = 0; i < parameters.Length; i++)
                    {
                        command.Parameters.Add(parameters[i]);
                    }
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

        public List<Expense> GetExpenses()
        {
            List<Expense> list = new List<Expense>();

            using (SQLiteCommand command = Connection.CreateCommand())
            {
                command.CommandText = @"
                    SELECT * 
                    FROM [Expense] 
                    ORDER BY [TransactionDate], [Item]
                ";
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(DataReaderToObject(reader));
                    }
                }
            }

            return list;
        }

        public List<Expense> GetExpenses(List<QueryMonth> months, List<Category> categories, bool excludeDisabled)
        {
            List<Expense> list = new List<Expense>();

            using (SQLiteCommand command = Connection.CreateCommand())
            {
                command.CommandText = BuildGetExpensesQuery(months, categories, excludeDisabled);
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(DataReaderToObject(reader));
                    }
                }
            }

            return list;
        }

        private string BuildGetExpensesQuery(List<QueryMonth> months, List<Category> categories, bool excludeDisabled)
        {
            StringBuilder query = new StringBuilder();
            StringBuilder monthClause = new StringBuilder();
            StringBuilder categoryCaluse = new StringBuilder();

            query.Append("SELECT * FROM [Expense] ");

            for (int i = 0; i < months.Count; i++)
            {
                if (i > 0 && i <= months.Count - 1)
                {
                    monthClause.Append(" OR ");
                }
                monthClause.Append(string.Format(" (date(TransactionDate) >= '{0}' and date(TransactionDate) <= '{1}')", months[i].StartDate.ToString("yyyy-MM-dd"), months[i].EndDate.ToString("yyyy-MM-dd")));
            }

            for (int i = 0; i < categories.Count; i++)
            {
                if (i > 0 && i <= categories.Count - 1)
                {
                    categoryCaluse.Append(", ");
                }
                categoryCaluse.Append(categories[i].CategoryID.ToString());
            }

            if (monthClause.Length > 0 || categoryCaluse.Length > 0)
            {
                query.Append(" WHERE ");
            }

            if (monthClause.Length > 0)
            {
                query.Append(" (" + monthClause.ToString() + ")");
            }

            if (monthClause.Length > 0 && categoryCaluse.Length > 0)
            {
                query.Append(" AND ");
            }

            if (categoryCaluse.Length > 0)
            {
                query.Append(" CategoryID IN (" + categoryCaluse.ToString() + ")");
            }

            if (monthClause.Length > 0 && categoryCaluse.Length > 0 && excludeDisabled)
            {
                query.Append(" AND IsDisabled = 0");
            }

            query.Append(" ORDER BY [TransactionDate], [Item]");

            return query.ToString();
        }

        public Expense GetExpense(int expenseID)
        {
            Expense item = null;

            using (SQLiteCommand command = Connection.CreateCommand())
            {
                SQLiteParameter parameter = new SQLiteParameter(DbType.Int32);
                parameter.Value = expenseID;

                command.CommandText = @"
                    SELECT * 
                    FROM [Expense] 
                    WHERE ExpenseID = ?
                ";
                command.Parameters.Add(parameter);
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        item = DataReaderToObject(reader);
                        break;
                    }
                }
            }

            return item;
        }

        public void DeleteExpense(int expenseID)
        {
            using (DbTransaction transaction = Connection.BeginTransaction())
            {
                using (SQLiteCommand command = Connection.CreateCommand())
                {
                    SQLiteParameter parameter = new SQLiteParameter(DbType.Int32);
                    parameter.Value = expenseID;

                    command.CommandText = @"
                        DELETE FROM [Expense] 
                        WHERE ExpenseID = ?
                    ";
                    command.Parameters.Add(parameter);
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

        private Expense DataReaderToObject(SQLiteDataReader reader)
        {
            Expense item = new Expense();
            item.ExpenseID = reader.GetInt32(reader.GetOrdinal("ExpenseID"));
            item.TransactionDate = reader.GetDateTime(reader.GetOrdinal("TransactionDate"));
            item.CategoryID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
            item.Item = reader.GetString(reader.GetOrdinal("Item"));
            item.Description = reader.GetString(reader.GetOrdinal("Description"));
            item.Amount = reader.GetFloat(reader.GetOrdinal("Amount"));
            item.GST = reader.GetFloat(reader.GetOrdinal("GST"));
            item.PST = reader.GetFloat(reader.GetOrdinal("PST"));
            item.Total = reader.GetFloat(reader.GetOrdinal("Total"));
            item.IsDisabled = reader.GetBoolean(reader.GetOrdinal("IsDisabled"));
            item.InsertDateTime = reader.GetDateTime(reader.GetOrdinal("InsertDateTime"));
            item.UpdateDateTime = reader.GetDateTime(reader.GetOrdinal("UpdateDateTime"));
            return item;
        }
    }
}
